/** Table Model based on JDBC  2.0 :
 
    An JTable model is defined for a SQL statement. This table model used some
features coming with JDBC 2.0 driver.
 
*/
 
import java.sql.*;
import javax.swing.table.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.io.*;
import java.util.*;
 
class JDBC2TableModel extends AbstractTableModel {
    String cTitles[] = { "Company_Name", "LastName", "FirstName",
                         "HOME No.", "Work No.", "EMail", "Pager", "Fax",
                         "Address", "City", "State", "Zip", "Memo" };
    JDBC2TableModel() {
        String  url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
                user="cs440", passwd="student1";
        try {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                cnn = DriverManager.getConnection(url, user, passwd);
                cnn.setAutoCommit(true);
                stmt = cnn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                getResultSet(sql);
        } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
    }
 
    void getResultSet( String sql ) {
        try {
                resultSet = stmt.executeQuery(sql);
                getColumnTitles();
                resultSet.last();
                recordCount = resultSet.getRow();
                resultSet.first();
        } catch (SQLException e ) { e.printStackTrace(); }
    }
    void reload() {
        try {
                getResultSet(sql);
                resultSet.last();
                recordCount = resultSet.getRow();
                resultSet.first();
                fireTableDataChanged();
                currentRow = 0;
        } catch (SQLException e ) { e.printStackTrace(); }
    }
    void getColumnTitles() {
        try {
                ResultSetMetaData meta = resultSet.getMetaData();
                columnCount = meta.getColumnCount();
                columnTitles = new Vector(columnCount);
                for ( int i = 1; i <= columnCount; i ++ )
                        columnTitles.add(ScreenIO.expandString(meta.getColumnName(i), 15));
        } catch( SQLException e) { e.printStackTrace(); }
    }
 
    public boolean isCellEditable(int row, int col) { return true; }
    public int getRowCount()    { return recordCount; }
    public int getColumnCount() { return 6; } // to display just first 6 columns
    public String getColumnName(int col) { return cTitles[col]; }
 
    public Object getValueAt(int row, int col) {
        try {
                resultSet.relative (row - currentRow); currentRow = row;
                return resultSet.getString(col+1) ;
        } catch ( SQLException e ) { e.printStackTrace() ; }
        return " ";
    }
   public String resultRowToString( int row ) {
      String tmp = "";
      try {
         resultSet.relative (row - currentRow); currentRow = row;
         for( int i = 1; i <= columnCount; i++ ) {
            tmp = tmp + resultSet.getString( i );
         }
      } catch ( SQLException e ) { e.printStackTrace() ; }
      return tmp;
   }
   public void makeTextFile( boolean colSelected[] ) {
      currentRow = FileIO.makeTextFile( resultSet, currentRow, recordCount, colSelected );
      return;
   }
   public void makeLabelFile() {
      currentRow =FileIO.makeLabelFile( resultSet, currentRow, recordCount );
      return;
   }
   public void  setValueAt( Object obj, int row, int col ) {
      try {
         resultSet.relative (row - currentRow); currentRow = row;
         resultSet.updateString(col+1, (String) obj);
         resultSet.updateRow();
         cnn.commit();
      } catch (SQLException e) { e.printStackTrace(); }
   }
 
   public void addRow( String[] newValues ) {
      try {
         resultSet.moveToInsertRow();
         for( int i = 0; i < 13; i++ )
            resultSet.updateString( i+1, newValues[i] );
         resultSet.insertRow();
         recordCount++;
         getResultSet(sql);
         fireTableDataChanged();
         currentRow = 0;
      } catch (SQLException e) { e.printStackTrace(); }
   }
 
   public Object removeRow( int row ) {
      try {
         resultSet.relative (row - currentRow);   currentRow = row;
         resultSet.deleteRow();
         recordCount--;
         getResultSet(sql);
         fireTableDataChanged();
         currentRow = 0;
      } catch (SQLException e ) { e.printStackTrace() ; }
      return "Hello";
    }
    public void saveData() {
        try { cnn.commit(); } catch (SQLException e) { e.printStackTrace(); }
    }
 
    public void requery(String name) {
        String tmp = sql + " WHERE lastName like '" + name + "%' OR firstName like '" + name + "%'" ;
        System.out.println(tmp);
        getResultSet(tmp);
        fireTableDataChanged();
        currentRow = 0;
    }

    class ColumnListener extends MouseAdapter {
         protected JTable clTable;

         public ColumnListener( JTable table ) {
            clTable = table;
         }
// This will sort the table when column header is clicked, alternating
// in ascending order and descending order
         public void mouseClicked( MouseEvent e ) {
            String tmp;
            TableColumnModel tcm = clTable.getColumnModel();
            int colIndex = tcm.getColumnIndexAtX( e.getX() );
            int modelIndex = tcm.getColumn( colIndex ).getModelIndex();
            String colName = getColumnName( modelIndex );
            if( modelIndex == 3 ) colName = new String( "phone1" );
            if( modelIndex == 4 ) colName = new String( "phone2" );

            if( sortAsc ) tmp = sql + " ORDER BY " + colName + " ASC";
            else tmp = sql + " ORDER BY " + colName + " DESC";

            System.out.println( "sql command: " + tmp );
            getResultSet(tmp);
            fireTableDataChanged();
            currentRow = 0;
            sortAsc = !sortAsc;           // toggle sort direction indicator
         }
      }


    // Data members
    String sql = "SELECT Company_Name, LastName, FirstName, Phone1, Phone2, EMail, Pager, Fax, Address, City, State, Zip, Memo FROM AddressBook ";
    Connection  cnn;
    Statement   stmt;
    ResultSet   resultSet;
    Vector      columnTitles;
    int currentRow = 0, columnCount = 0, recordCount = 0;
    boolean sortAsc = true;
    File file = null;
}

